package com.example.test.repo;

import com.example.test.entity.Weather1;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

/**
 * 日期：2022/11/5
 * 作者：flag
 * 功能：
 */
public interface Weather1Repository extends JpaRepository<Weather1, Integer> {

    @Query(value = "SELECT\n" +
            "	l.CODE code,\n" +
            "	l.NAME name,\n" +
            "	mtmp.maxhightmp,\n" +
            "	w.date_ AS maxhightmpdate,\n" +
            "	c.lianxu_days hightmpdays \n" +
            "FROM\n" +
            "	location l\n" +
            "	LEFT JOIN ( SELECT location, max( hightmp ) maxhightmp FROM weather1 GROUP BY location ) mtmp ON mtmp.location = l.\n" +
            "	CODE LEFT JOIN ( SELECT * FROM weather1 ) w ON w.location = mtmp.location \n" +
            "	AND w.hightmp = mtmp.maxhightmp\n" +
            "	LEFT JOIN (\n" +
            "	SELECT\n" +
            "		location,\n" +
            "		max( days ) lianxu_days,\n" +
            "		min( login_day ) start_date,\n" +
            "		max( login_day ) end_date \n" +
            "	FROM\n" +
            "		(\n" +
            "		SELECT\n" +
            "			location,\n" +
            "			@cont_day\\:=(\n" +
            "			CASE\n" +
            "					\n" +
            "					WHEN ( @last_uid = location AND DATEDIFF( login_dt, @last_dt )= 1 ) THEN\n" +
            "					( @cont_day + 1 ) \n" +
            "					WHEN ( @last_uid = location AND DATEDIFF( login_dt, @last_dt )< 1 ) THEN\n" +
            "					( @cont_day + 0 ) ELSE 1 \n" +
            "				END \n" +
            "					) AS days,(\n" +
            "					@cont_ix\\:=(\n" +
            "						@cont_ix +\n" +
            "					IF\n" +
            "					( @cont_day = 1, 1, 0 ))) AS cont_ix,\n" +
            "				@last_uid\\:=location,\n" +
            "				@last_dt\\:=login_dt login_day \n" +
            "			FROM\n" +
            "				( SELECT location, DATE( date_ ) AS login_dt FROM weather1 WHERE hightmp > 40 ORDER BY location, date_ ) AS t,(\n" +
            "				SELECT\n" +
            "					@last_uid\\:='',\n" +
            "					@last_dt\\:='',\n" +
            "					@cont_ix\\:=0,\n" +
            "					@cont_day\\:=0 \n" +
            "				) AS t1 \n" +
            "			) AS t2 \n" +
            "		GROUP BY\n" +
            "			location,\n" +
            "		cont_ix \n" +
            "	) c ON c.location = l.CODE order by mtmp.maxhightmp desc", nativeQuery = true)
    List<Map> getWeatherMaxTmp();
}
